#!/usr/bin/env python3.1
from sqlalchemy import create_engine
from sqlalchemy import Table,Column,Integer,String,ForeignKey,Sequence
from sqlalchemy import MetaData
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import mapper
engine = create_engine('sqlite:///foo.db', echo = True)
metadata = MetaData()
items_table = Table('items', metadata,  #both topic and reply are treated as item
                    Column('id', Integer, Sequence('item_id_seq') ,primary_key = True),
                    Column('owner_id',Integer),
                    Column('tittle', String(100)),
                    Column('post_date', Integer),
                    Column('last_modify_date', Integer),
                    Column('content', String(5000)),
                    Column('type', Integer) #0 for topic, 1 for reply
                    )

topics_reply_table = Table('topic_reply', metadata,
                    Column('topic_id', Integer), 
                    Column('reply_id', Integer)
                    )
users_table = Table('users', metadata,
                    Column('id', Integer, Sequence('user_id_seq'), primary_key = True),
                    Column('name', String(50)),
                    Column('password', String(50)), 
                    Column('group_id', Integer),
                    Column('total_time', Integer),
                    Column('reg_time', Integer),
                    Column('last_login_time', Integer)
                    )
                           
group_table = Table('groups', metadata,
                    Column('id', Integer),
                    Column('name', String(50))
                    )

metadata.create_all(engine)

class Item(object):
    def __init__(self,id,owner_id,tittle,post_date,last_modify_date,content,type):
        self.id = id
        self.owner_id=owner_id
        self.tittle=tittle
        self.post_date=post_date
        self.last_modify_date=last_modify_date
        self.content=content
        self.type=type
    def __repr__(self):
        return "<Item('%s','%s','%s','%s','%s','%s','%s','%s')>" % (
            self.id,
            self.owner_id,
            self.tittle,
            self.post_date,
            self.last_modify_date,
            self.content,
            self.type )

class User(object):
    def __init__(self,id,name,password,group_id,total_time,reg_time,last_login_time):
        self.id = id
        self.name=name
        self.password=password
        self.group_id=group_id
        self.total_time=total_time
        self.reg_time=reg_time
        self.last_login_time=last_login_time
    def __repr__(self):
        return "<User('%s','%s','%s','%s','%s','%s','%s')>" % (
            self.id,
            self.name,
            self.password,
            self.group_id,
            self.total_time,
            self.reg_time,
            self.last_login_time
            )
class TopicReply(object):
    def __init__(self, topic_id, reply_id):
        self.topic_id = topic_id
        self.reply_id = reply_id
    def __repr__(self):
        return "<TopicReply('%s','%s')>" %(self.topic_id, self.reply_id)

class Group(object):
    def __init__(self, id, name):
        self.id = id
        self.name = name

    def __repr__(self):
        return "<Group('%s', '%s')>" % (self.id, self.name)



mapper(Item, items_table)
mapper(User, users_table)
mapper(TopicReply, topics_reply_table)
mapper(Group, group_table)



